<?php
// 用户进行对于数据库的操作
namespace App\Models;

use App\Config\Database;
use PDO;

class User{
    private $conn;
    private $table = 'students';

    public function __construct() {
        //数据库的初始化
        $database = new Database();
        $this->conn = $database->getConnection();
    }

    /**
     * 获取所有学生信息
     * @return array
     */
    public function getAll() {
        $query = "SELECT sid, name, major, gender, birth, credits, photo, remark FROM " . $this->table . " ORDER BY sid DESC";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 获取指定id的学生信息
     * @param string $id
     * @return array
     */
    public function getById($id) {
        $query = "SELECT sid, name, major, gender, birth, credits, photo, remark FROM " . $this->table . " WHERE sid = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * 创建学生信息
     * @param array $data
     * @return bool
     */
    // 创建
    public function create($data) {
        $query = "INSERT INTO students (sid, name, major, gender, birth, credits, photo, remark) 
                VALUES (:sid, :name, :major, :gender, :birth, :credits, :photo, :remark)";
        $stmt = $this->conn->prepare($query);
        
        // 将数组元素赋值给变量
        $sid = $data['sid'];
        $name = $data['name'];
        $major = $data['major'];
        $gender = $data['gender'];
        $birth = $data['birth'];
        $credits = $data['credits'];
        $photo = $data['photo'] ?? null;
        $remark = $data['remark'] ?? null;
        
        // 绑定变量
        $stmt->bindParam(':sid', $sid);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':major', $major);
        $stmt->bindParam(':gender', $gender);
        $stmt->bindParam(':birth', $birth);
        $stmt->bindParam(':credits', $credits);
        $stmt->bindParam(':photo', $photo);
        $stmt->bindParam(':remark', $remark);

        return $stmt->execute();
    }
    /**
     * 更新学生信息
     * @param string $sid
     * @param array $data
     * @return bool
     */
    public function update($sid, $data) {
        $query = "UPDATE students SET 
                name = :name, 
                major = :major, 
                gender = :gender, 
                birth = :birth, 
                credits = :credits, 
                photo = :photo, 
                remark = :remark 
                WHERE sid = :sid";
    
        $stmt = $this->conn->prepare($query);
        
        // 先将值赋给变量，然后绑定
        $name = $data['name'] ?? '';
        $major = $data['major'] ?? '';
        $gender = $data['gender'] ?? 0;
        $birth = $data['birth'] ?? null;
        $credits = $data['credits'] ?? 0;
        $photo = $data['photo'] ?? null;
        $remark = $data['remark'] ?? null;
        
        $stmt->bindParam(':sid', $sid);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':major', $major);
        $stmt->bindParam(':gender', $gender);
        $stmt->bindParam(':birth', $birth);
        $stmt->bindParam(':credits', $credits);
        $stmt->bindParam(':photo', $photo);
        $stmt->bindParam(':remark', $remark);
    
        return $stmt->execute();
    }

    /**
     * 删除学生信息
     * @param string $sid
     * @return bool
     */
    public function delete($sid) {
        $query = "DELETE FROM " . $this->table . " WHERE sid = :sid";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':sid', $sid);

        return $stmt->execute();
    }
    
    /**
     * 搜索学生信息
     * @param string $sid
     * @param string $name
     * @param string $major
     * @param int $page
     * @param int $pageSize
     * @return array
     */
    // 详细搜索的操作，通过用户的sid字段和name字段进行模糊查询对应的学生
    public function search($sid, $name, $major, $page = 1, $pageSize = 10) {
        $query = "SELECT sid, name, major, gender, birth, credits, photo, remark FROM " . $this->table;
        $conditions = [];
        $params = [];
        
        if (!empty($sid)) {
            $conditions[] = "sid LIKE :sid";
            $params[':sid'] = "%$sid%";
        }
        if (!empty($name)) {
            $conditions[] = "name LIKE :name";
            $params[':name'] = "%$name%";
        }
        if (!empty($major)) {
            $conditions[] = "major LIKE :major";
            $params[':major'] = "%$major%";
        }
        
        if (!empty($conditions)) {
            $query .= " WHERE " . implode(" AND ", $conditions);
        }

        // 计算总记录数（用于分页）
        $countQuery = "SELECT COUNT(*) as total FROM " . $this->table;
        if(!empty($conditions)){
            $countQuery .= " WHERE " . implode(" AND ", $conditions);
        }

        $countStmt = $this->conn->prepare($countQuery); // 创建一个模糊查询的语句对象
        
        foreach ($params as $param => $value) {
            $countStmt->bindValue($param, $value); // 用 bindValue 避免引用问题
        }
        
        $countStmt->execute(); // 必须添加执行步骤
        $result = $countStmt->fetch(PDO::FETCH_ASSOC);
        $total = $result['total']; // 正确获取total 值

        // 计算总页数
        $totalPages = ceil($total / $pageSize);

        // 确保页码在有效范围内,如果超出了就变为1从新开始
        $page = max(1, min($page, $totalPages));

        // 添加分页
        $offset = ($page - 1) * $pageSize;
        $query .= " LIMIT :offset, :pageSize";
        
        $stmt = $this->conn->prepare($query);
        
        
        foreach ($params as $param => $value) {
            $stmt->bindValue($param, $value);
        }

        // 绑定分页参数
        $stmt->bindValue(':offset', $offset,PDO::PARAM_INT);
        $stmt->bindValue(':pageSize', $pageSize,PDO::PARAM_INT);

        // 执行主查询
        $stmt->execute();
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        return [
            'data' => $results, // 匹配的记录
            'total' => $total, // 返回总记录数
            'page' => $page,  // 返回当前页码
            'pageSize' => $pageSize, // 返回每页大小 
            'totalPages' => $totalPages // 返回总页数
        ];
    }
}
?>